from sqlalchemy import create_engine,func
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker,relationship
from sqlalchemy import Column,Integer, String, Date
from sqlalchemy.dialects.mysql import DOUBLE



Base = declarative_base()

class Emp(Base):
    __tablename__ = 'emp'
    empno = Column(Integer, primary_key=True, autoincrement=False)
    ename = Column(String(10))
    job = Column(String(9))
    mgr = Column(Integer)
    hiredate = Column(Date)
    sal = Column(DOUBLE(7,2))
    comm = Column(DOUBLE(7,2))
    deptno = Column(Integer)


class Dept(Base):
    __tablename__ = 'dept'
    deptno = Column(Integer, primary_key=True, autoincrement=False)
    dname = Column(String(14))
    loc = Column(String(13))

class Ormdb(object):
    def __init__(self, username, password, host, port, dbname):
        sql = 'mysql+pymysql://{0}:{1}@{2}:{3}/{4}?charset=utf8'.format(username, password, host, port, dbname)
        engine = create_engine(sql, max_overflow=5)
        Session = sessionmaker(bind=engine)
        self.session = Session()
    def add_obj(self,order_list):

        self.session.add_all(order_list)
        self.session.commit()
    def search(self):
        ret = self.session.query(Emp, Dept).filter(Emp.deptno == Dept.deptno).all()
        return ret
    def search_deptno(self):
        ret = self.session.query(Emp, Dept).filter(Emp.deptno==20,Emp.deptno == Dept.deptno).all()
        return ret
    def search_avg(self):
        ret = self.session.query(Dept.dname,func.avg(Emp.sal)).group_by(Dept.dname.desc()).all()
        return ret
#Session.query(funct.avg(字段),obj.条件字段).group_by(条件字段).all()

    def search_info(self):
        ret = self.session.query(Emp).join(Dept,Emp.deptno == Dept.deptno,isouter=True).all()
        return ret

    def info(self):
        # 使用SQLalchemy，查询岗位薪资低于3000元，或者入职时间晚于1981 - 10 - 01
        # 的员工姓名、编号，岗位，薪资、入职时间，同时对薪资降序和入职时间升序排列
        ret = self.session.query(Emp).order_by(Emp.sal.asc()).filter(Emp.sal<3000).all()
        return ret
# session.query(obj1).join(obj2,isouter=True).all()
if __name__ == '__main__':

    order_list = [Emp(empno=7369, ename='SMITH', job='CLERK', mgr=7902, hiredate='1980-12-17', sal=800, deptno=20),
                 Emp(empno=7499, ename='ALLEN', job='SALESMAN', mgr=7698, hiredate='1981-02-20', sal=1600, comm=300,deptno=30),
                 Emp(empno=7521, ename='WARD', job='SALESMAN', mgr=7698, hiredate='1981-02-22', sal=1250, comm=500,deptno=30),
                 Emp(empno=7566, ename='JONES', job='MANAGER', mgr=7839, hiredate='1981-04-02', sal=2975,deptno=20),
                 Emp(empno=7654, ename='MARTIN', job='SALESMAN', mgr=7698, hiredate='1981-09-28', sal=1250, comm=1400,deptno=30),
                 Emp(empno=7698, ename='BLAKE', job='MANAGER', mgr=7839, hiredate='1981-05-01', sal=2850,deptno=30),
                 Emp(empno=7782, ename='CLARK', job='MANAGER', mgr=7839, hiredate='1981-06-09', sal=2450,deptno=10),
                 Emp(empno=7788, ename='SCOTT', job='ANALYST', mgr=7566, hiredate='1987-04-19', sal=3000,deptno=20),
                 Emp(empno=7839, ename='KING', job='PRESIDENT', hiredate='1981-11-17', sal=5000, deptno=10),
                 Emp(empno=7844, ename='TURNER', job='SALESMAN', mgr=7698, hiredate='1981-09-08', sal=1500, comm=0,deptno=30),
                 Emp(empno=7876, ename='ADAMS', job='CLERK', mgr=7788, hiredate='1987-05-23', sal=1100, deptno=20),
                 Emp(empno=7900, ename='JAMES', job='CLERK', mgr=7698, hiredate='1981-12-03', sal=950, deptno=30),
                 Emp(empno=7902, ename='FORD', job='ANALYST', mgr=7566, hiredate='1981-12-03', sal=3000,deptno=20),
                 Emp(empno=7934, ename='MILLER', job='CLERK', mgr=7782, hiredate='1982-01-23', sal=1300,deptno=10),
                 Dept(deptno=10,dname='ACCOUNTING',loc='NEW YORK'),
                 Dept(deptno=20,dname='RESEARCH',loc= 'DALLAS'),
                Dept(deptno=30, dname='SALES', loc='CHICAGO'),
                Dept(deptno=40, dname='OPERATIONS', loc='BOSTON')]
    db = Ormdb('root', '123456', '127.0.0.1', 3306, 'py04db')
    # 注意插入成功后不能重复插入 否则会报错
    ## db.add_obj(order_list)

    # 1.查找全部数据 循环出来
    # order =db.search()
    # print(order)
    # for item in order:
    #     print(item.Emp.empno,item.Emp.ename,item.Emp.job,item.Emp.mgr,item.Dept.dname,item.Dept.loc)

    # 2.查找部门编号20的员工信息及部门名
    # order=db.search_deptno()
    # for item in order:
    #     print(item.Emp.empno,item.Emp.ename,item.Emp.job,item.Emp.mgr,item.Emp.sal,item.Emp.hiredate,item.Dept.dname)

    # 3查询所有部门的平均工资，并按照降序排列
    # order = db.search_avg()
    # print(order)
    # 4
    # order = db.search_info()
    # # print(order)
    # for item in order:
    #     print(item.empno,item.ename,item.job,item.mgr,item.sal)
    # 5
    order = db.info()
    for item in order:
        print(item.empno,item.ename,item.job,item.mgr,item.sal,item.hiredate)